﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Dados.ConexaoCE;
using System.Collections;
using System.Drawing;
using System.IO;

namespace EFITransmissao.Dados.DAO
{
    public class DAOLeitura
    {
        public DAOLeitura() { }

        public static DataRow ObterLeiturasIdFoto(int rowIndex)
        {
            DataRow retorno = null;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                retorno = cems.ExecutaConsultaDataRowLeituras("ObterLeiturasIdFoto", null, rowIndex);
                         
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return retorno;
        }

        public static Bitmap ObterFoto(string idFoto)
        {
            Hashtable prm = new Hashtable();
            prm["@idFoto"] = idFoto;
            
            byte[] foto = null;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                DataTable dt =  cems.ExecutaConsulta("ObterFoto", prm);

                foreach (DataRow dr in dt.Rows)
                {
                    foto = (byte[])dr["Foto"];
                }
                MemoryStream ms = new MemoryStream(foto);
                Bitmap bmap = new Bitmap(ms);
                return bmap;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }            
        }

        public static void AtualizarFoto(byte[] imagem, string idFoto)
        {
            Hashtable prm = new Hashtable();
            prm["@idFoto"] = idFoto;
            prm["@foto"] = imagem;

            try
            {                
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                cems.ExecutaAtualizacao("AtualizaFoto", prm);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        public static void AtualizarFotoOcorrencia(byte[] imagem, string idFoto)
        {
            Hashtable prm = new Hashtable();
            prm["@idFoto"] = idFoto;
            prm["@foto"] = imagem;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                cems.ExecutaAtualizacao("AtualizaFotoOcorrencia", prm);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return;
        }

        public static DataTable ObterTodosControle()
        {
            DataTable retorno = new DataTable();
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                retorno = cems.ExecutaConsulta("ObterTodosControle", null);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return retorno;
        }

        public static DataTable ObterTodosLeitura()
        {
            DataTable retorno = new DataTable();
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";
                                
                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                retorno = cems.ExecutaConsulta("ObterTodosLeituras", null);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return retorno;
        }

        public static DataTable ObterTodosLeituraEnderecos()
        {
            DataTable retorno = new DataTable();
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                retorno = cems.ExecutaConsulta("ObterTodosLeituraEnderecos", null);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return retorno;
        }

        public static DataTable ObterTodosCadMedidor()
        {
            DataTable retorno = new DataTable();
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                retorno = cems.ExecutaConsulta("ObterTodosCadMedidor", null);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return retorno;
        }

        public static DataTable ObterTodosTrocaRota()
        {
            DataTable retorno = new DataTable();
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                retorno = cems.ExecutaConsulta("ObterTodosTrocaRota", null);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return retorno;
        }

        public static void LimparStatusRoteiroRecebido()
        {            
            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                cems.ExecutaAtualizacao("LimparStatusRoteiroRecebido", null);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return ;
        }

        public static void LimparStatusLeiturasEnviadas()
        {

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                cems.ExecutaAtualizacao("LimparStatusLeiturasEnviadas", null);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return;
        }

        private static List<string> ObterTodasOcorrencias(string uc)
        {
            List<string> lst = new List<string>();
            Hashtable prm = new Hashtable();
            prm["@uc"] = uc;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                                    Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";
                
                CEMSDataAccess db = new CEMSDataAccess(_stringconexao);
                DataTable dt = db.ExecutaConsulta("ObterTodasOcorrencias", prm);

                foreach (DataRow dr in dt.Rows)
                {
                    for (int x = 1; x < 5; x++)
                    {
                        if (dr["Ocorrencia" + x].ToString() != string.Empty &&
                            dr["Ocorrencia" + x].ToString() != "65")
                                lst.Add(dr["Ocorrencia" + x].ToString());

                    }                    
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return lst;
        }

        public static void AtualizarOcorrencia(string uc)
        {
            List<string> lst = new List<string>();
            Hashtable prm = new Hashtable();
            prm["@uc"] = uc;

            try
            {
                string _stringconexao = @"Persist Security Info = false; 
                                        Data Source = .\Program Files\EFI_Sulgipe\Leituras.sdf";

                CEMSDataAccess cems = new CEMSDataAccess(_stringconexao);
                int posicao = 1;
                
                lst = ObterTodasOcorrencias(uc);

                if (lst.Count == 0)
                {
                    prm["@ocor1"] = string.Empty;
                    prm["@ocor2"] = string.Empty;
                    prm["@ocor3"] = string.Empty;
                    prm["@ocor4"] = string.Empty;
                }
                else
                {
                    foreach (string item in lst)
                    {
                        switch (posicao)
                        {
                            case 1:
                                prm["@ocor1"] = item.ToString();
                                prm["@ocor2"] = string.Empty;
                                prm["@ocor3"] = string.Empty;
                                prm["@ocor4"] = string.Empty;
                                break;
                            case 2:
                                prm["@ocor2"] = item.ToString();
                                prm["@ocor3"] = string.Empty;
                                prm["@ocor4"] = string.Empty;
                                break;
                            case 3:
                                prm["@ocor3"] = item.ToString();
                                prm["@ocor4"] = string.Empty;
                                break;
                            case 4:
                                prm["@ocor4"] = item.ToString();
                                break;
                            default:
                                break;
                        }
                        posicao++;
                    }
                }
                cems.ExecutaAtualizacao("UpdateLeituraOcorrencia", prm);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return;
        }

        #region Banco Leitura: Table Controle

        public static DataTable ObterKeyTblControle()
        {
            DataTable retorno = new DataTable();
            try
            {
                CEMSDataAccess CEDA = new CEMSDataAccess(@"Persist Security Info = false; 
                                                           Data Source=.\Program Files\EFI_Sulgipe\Leituras.sdf");

                retorno = CEDA.ExecutaConsulta("ObterTodosControle", null);
                    
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return retorno;
        }

        #endregion
    }
}
